package group2projman;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class PersistenceLayer {
	
	String dbUrl, user, password;
	
	ArrayList<User> users;
	
	public PersistenceLayer(String pDbUrl, String pUser, String pPassword) {
		dbUrl = pDbUrl;
		user = pUser;
		password = pPassword;
	}
	
	/**
	 * @param dbUrl The URL of the 
	 */
	public void saveAll() {
		Connection connection;
		
		try {								
			// Import/init driver
			Class.forName("org.hsqldb.jdbc.JDBCDriver"); 
			
			connection = DriverManager.getConnection(dbUrl, user, password);
			
			// Save system
			// Create table if necessary
			// Save catalogue
			
			// Save users
			
			
			
			// ###################### TEST CODE ###################### 			
			String tableName = "Members";
			
			Member test = new Member(201);
			test.transactionHistory.addTransaction(new Payment(17, 200));
			test.transactionHistory.addTransaction(new Payment(19, 131));
			// save member
			String statement = saveMemberStatement(test, connection);
			
			connection.createStatement().executeUpdate(statement);
			
			// test
			ResultSet result = connection.createStatement().executeQuery("SELECT * from " + tableName);
			
			while (result.next())
				System.out.println(result.getString("name") + " " + result.getString("password") + " " + result.getString("permission") + " " + result.getInt("id"));

			System.out.println("TRANSACTIONS:");
			result = connection.createStatement().executeQuery("SELECT * from Transactions201");
			
			while (result.next())
				System.out.println(result.getString("id") + " " + result.getString("amount"));
			
			// ###################### END OF TEST CODE ######################			
			
			
			
			
			
			// TODO: handle exceptions
			// Save users
								
		} catch (Exception e) {
			System.err.println("Error acessing database");
			System.err.println(e.getMessage());
			e.printStackTrace();
			return;
		}
		
	}
	
	public void loadAll() {
		//throw new Exception("Implement me");
	}
	
	private String saveUserStatement(User user, Connection connection)
			throws SQLException, Exception {
		String tableName = null;
		
		if (user.permission == User.Permission.MEMBER)
			return saveMemberStatement((Member)user, connection);
		else if (user.permission == User.Permission.STAFF)
			tableName = "Staff";
		else if (user.permission == User.Permission.ADMINISTRATOR)
			tableName = "Administrators";
		
		if (tableName == null)
			throw new Exception("Unknown user permission; cannot save user");
			
		return saveAttributesStatement(user.listAttributes(), tableName, connection);
	}
	
	private String saveMemberStatement(Member member, Connection connection)
			throws SQLException {
		// Shallow-save member
		StringBuffer result = new StringBuffer();
		result.append(saveAttributesStatement(member.listAttributes(), "Members", connection));
		
		// Create transaction table, if nonexistent		
		boolean tableExists = true;
		
		String transactionTableName = "Transactions" + member.getId();

		// TODO: fix
		/*ResultSet tableCheck = connection.getMetaData().getTables(null, null, transactionTableName, null);

		if (!tableCheck.next()) {
			// Table does not exist; create
			tableExists = false;
			String attributes = "int:id;1,dat:startDate;1,str:type;1,int:bookCopyId;1,dat:dueDate;1,int:lateFee;1,int:amount;1,STR:description;1";
			result.append(createTableStatement(transactionTableName,attributes));
		}*/
		// ^ This doesn't work, for some reason. Trying to figure out whether table transactionTableName exists
		
		// Save transaction history		
		for (Transaction t : member.transactionHistory.transactions)
			result.append(saveAttributesStatement(t.listAttributes(), transactionTableName, connection, tableExists));

		return result.toString();		
	}
	
	/**
	 * Given an attribute String, converts it into an SQL snippet
	 * TODO: is snippet the right term, lol?
	 * TODO: rename, add some input validation
	 * TODO: attribute generates table name
	 */
	private String createTableStatement(String tableName, String attributes) {
		StringBuffer result = new StringBuffer();		
		result.append("CREATE TABLE " + tableName + " (");
		
		// Reminder: format is type:typeName;value
		String[] split = attributes.split(",");
		
		// Confirm that attribute string is headed by an id attribute
		String[] idCheck = split[0].split("[:;]");

		if (!idCheck[0].equals("int") || !idCheck[1].equals("id"))
			throw new RuntimeException("Attribute string not headed by unique ID");
		
		result.append("id INT PRIMARY KEY, ");
		
		// Split list into attribute strings
		for (int i = 1; i < split.length; ++i) {
			// Split attribute into type, type name, and value strings
			String[] subSplit = split[i].split("[:;]");
			
			result.append(subSplit[1]).append(" ");					
			
			if (subSplit[0].equals("int")) {
				result.append("INT");
			} else if (subSplit[0].equals("str")) {				
				result.append("VARCHAR(20)");				
			} else if (subSplit[0].equals("STR")) {
				result.append("VARCHAR(200)");				
			} else if (subSplit[0].equals("dat")) {
				result.append("VARCHAR(28)");				
			} else
				throw new RuntimeException("Unrecognized attribute type");
			
			result.append(", ");							
		}
		
		// Remove trailing comma and space
		result.delete(result.length() - 2, result.length());
		
		result.append(") ");
		
		return result.toString();
	}
	
	private String saveAttributesStatement(String attributes, String tableName, Connection connection, boolean tableExists)
	throws SQLException {		
		StringBuffer result = new StringBuffer();
		
		// Reminder: format is type:typeName;value
		String[] split = attributes.split(",");
		
		// Is the object already in the table?
		if (tableExists) {
			String[] subSplit = split[0].split("[:;]");
		
			if (!subSplit[0].equals("int") || !subSplit[1].equals("id")) // Confirm that first element is ID
				throw new RuntimeException("Attribute string not headed by unique ID");
			
			int id = Integer.parseInt(subSplit[2]);
			
			ResultSet idCheck = connection.createStatement().executeQuery("SELECT * FROM " + tableName +
					" WHERE id=" + id);
		
			if (idCheck.next()) {
				// Yes. Update entry
				result.append("UPDATE " + tableName + " SET ");				
				result.append(updateAttributesThing(split));
				result.append(" WHERE id=" + id + " ");
				
				return result.toString();
			}
		}
		
		// Object is not in table. Create new entry
		result.append("INSERT INTO " + tableName + " VALUES(");
		result.append(subSaveAttributesThing(split));
		result.append(") ");

		return result.toString();
	}
	
	private String saveAttributesStatement(String attributes, String tableName, Connection connection)
			throws SQLException {
		return saveAttributesStatement(attributes, tableName, connection, true);
	}
	
	private String updateAttributesThing(String[] split) {
		StringBuffer result = new StringBuffer();
		
		for (int i = 1; i < split.length; ++i)
			result.append(parseAttribute(split[i], true));
		
		// Remove trailing comma and space
		result.delete(result.length() - 2, result.length());
		
		return result.toString();
	}
	
	/// TODO: rename, add some input validation
	private String subSaveAttributesThing(String[] split) {
		StringBuffer result = new StringBuffer();
		
		// Split list into attribute strings
		for (String s : split) {	
			// Parse said strings, convert to SQL snippet
			result.append(parseAttribute(s));
		}
		
		// Remove trailing comma and space
		result.delete(result.length() - 2, result.length());
		
		return result.toString();
	}
	
	private String parseAttribute(String attribute, boolean assignment) {
		// Split attribute into type, type name, and value strings
		String[] split = attribute.split("[:;]");
		
		StringBuffer result = new StringBuffer();
		
		if (assignment) {
			result.append(split[1]);
			result.append("=");
		}
		
		if (split[0].equals("str") || split[0].equals("STR")
				|| split[0].equals("dat"))
			result.append("'").append(split[2]).append("'");
		else
			result.append(split[2]);
		
		result.append(", ");
		
		return result.toString();		
	}
	
	private String parseAttribute(String attribute) {
		return parseAttribute(attribute, false);		
	}


}
